#!/usr/bin/env node

const mysql = require('mysql2/promise'),
      log   = console.log,
      Koa   = require('koa'),
      app   = new Koa(),
      Router = require('koa-router'),
      router = new Router(),
      fs    = require('fs'),
      path  = require('path'),
      cors  = require('koa2-cors'),
      http  = require('http'),
      https = require('https');

let con = mysql.createPool({
    host:'154.8.156.240',
    port:'2020',
    user:'root',
    password:'Ljj0222.',
    database:'tuijian'
});
//发布量
async function getshouye(){
  const sql = `select share_id from main_table order by share_id desc`;
  let [rows] = await con.execute(sql);
  return rows;
}
//用户量
async function getshouyenum(){
  const sql = `select id from user_table`;
  let [rows] = await con.execute(sql);
  return rows;
}
//用户信息
async function getxinxi(id){
  const sql = `select * from user_table where id = '${id}'`;
  let [rows] = await con.execute(sql);
  return rows;
}
async function getxinxi1(){
  const sql = `select * from user_table`;
  let [rows] = await con.execute(sql);
  return rows;
}

//关注商家数
async function getshopnum(user_id){
  const sql = `select * from concern_shop_table where user_id = '${user_id}' and state = 1`;
  let [rows] = await con.execute(sql);
  return rows;
}
//关注用户数
async function getdavnum(user_id){
  const sql = `select * from concern_dav_table where user_id = '${user_id}' and state = 1`;
  let [rows] = await con.execute(sql);
  return rows;
}
//粉丝数
async function getfensinum(user_id){
  const sql = `select * from concern_dav_table where dav_id = '${user_id}' and state = 1`;
  let [rows] = await con.execute(sql);
  return rows;
}
//分享审核
async function getshenhe(judge){
  const sql = `select share_id,user_name,user_img,img1,title,text,judge,img1 from main_table left join user_table on user_table.id = main_table.user_id left join img_table on img_table.id = main_table.share_id where judge = '${judge}'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//分享详情
async function getid_home(id){
  const sql = `select share_id,shop_id,title,text,date,addr,user_name,user_img,date,addr,food,tags,top,img1,img2,img3,img4,img5,img6,judge from main_table left join user_table on user_table.id = main_table.user_id left join img_table on  main_table.share_id = img_table.id where main_table.share_id = '${id}' `;
  let [rows] = await con.execute(sql);
  return rows;
}
//审核中
async function getshenhezhong(share_id,judge){
  const sql = `update main_table set judge='${judge}' where share_id = '${share_id}'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//商家
async function getshop(){
  const sql =`select * from Merchants_settled_table`;
  let [rows] = await con.execute(sql);
  return rows;
}
async function getshop1(state){
  const sql =`select * from Merchants_settled_table where state = '${state}'`;
  let [rows] = await con.execute(sql);
  return rows;
}
async function getshop2(id){
  const sql =`select * from Merchants_settled_table where id = ${id}`;
  let [rows] = await con.execute(sql);
  return rows;
}
//商家审核
async function getshopsh(id,state){
  const sql = `update Merchants_settled_table set state='${state}' where id = ${id}`;
  let [rows] = await con.execute(sql);
  return rows;
}
//商家模糊搜索
async function getss(name){
  const sql =`select * from Merchants_settled_table where shop_name like '%${name}%' or shop_addr like '%${name}%'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//详情页模糊搜索
async function getxqss(id,name){
  const sql = `select share_id,user_name,user_img,img1,title,text,judge,img1 from main_table left join user_table on user_table.id = main_table.user_id left join img_table on img_table.id = main_table.share_id `;
  let [rows] = await con.execute(sql);
  return rows;
}
//评论区展示
async function getpl(){
  const sql = `select * from comments_table`;
  let [rows] = await con.execute(sql);
  return rows;
}
async function getpl1(){
  const sql = `select comments_table.id,share_id,user_id,date,text,zan,state,user_name from comments_table,user_table where user_table.id = comments_table.user_id`;
  let [rows] = await con.execute(sql);
  return rows;
}

async function getname(id){
  const sql = `select * from user_table where id = '${id}'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//删除评论
async function delpl(id){
  const sql = `delete from comments_table where id = '${id}'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//查看评论
async function gettext(text){
  const sql = `select * from comments_table where text like '%${text}%'`;
  let [rows] = await con.execute(sql);
  return rows;
}
(async function(){
    //log(await getAreas());
})();

module.exports.getshouye = getshouye;
module.exports.getshouyenum = getshouyenum;
module.exports.getxinxi = getxinxi;
module.exports.getshopnum = getshopnum;
module.exports.getdavnum = getdavnum;
module.exports.getfensinum = getfensinum;
module.exports.getshenhe = getshenhe;
module.exports.getid_home = getid_home;
module.exports.getshenhezhong = getshenhezhong;
module.exports.getshop = getshop;
module.exports.getxinxi1 = getxinxi1;
module.exports.getss = getss;
module.exports.getxqss = getxqss;
module.exports.getpl = getpl;
module.exports.getpl1 = getpl1;
module.exports.delpl = delpl;
module.exports.gettext = gettext;
module.exports.getname = getname;
module.exports.getshop1 = getshop1;
module.exports.getshop2 = getshop2;
module.exports.getshopsh = getshopsh;


